package com.news.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.news.entity.News;
import com.news.util.JDBCUtil;

/**
 * 新闻数据访问类
 *
 */
public class NewsDao {
	JDBCUtil util =JDBCUtil.getInstance();
	/**
	 * 获取新闻列表
	 * @return
	 */
	public List<News> selectList(){
		String sql="select * from tb_news order by id ";
		ResultSet rs =util.query(sql);
		List<News> news1=new ArrayList<News>();
		try {
			while(rs.next()){
				String title=rs.getString("title");
				String content=rs.getString("content");
				String image_url=rs.getString("image_url");
				String news_type=rs.getString("news_type");
				int user_id=rs.getInt("user_id");
				int id=rs.getInt("id");
				News news =new News();
				news.setTitle(title);
				news.setContent(content);
				news.setImage_url(image_url);
				news.setNews_type(news_type);
				news.setUser_id(user_id);
				news.setId(id);
				news1.add(news);
			}
			util.close();
			return news1;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	/**
	 * 新增新闻
	 * @param news
	 * @return
	 */
	public boolean insert(News news){
		Date queueDate = new Date();
		SimpleDateFormat queueDateFormat= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String pub_time = queueDateFormat.format(queueDate);
		JDBCUtil util=JDBCUtil.getInstance();
		String sql ="insert into tb_news(title,content,image_url,news_type,user_id,pub_time)"+"values(?,?,?,?,?,?)";
		List<Object> params=new ArrayList<>();
		params.add(news.getTitle());
		params.add(news.getContent());
		params.add(news.getImage_url());
		params.add(news.getNews_type());
		params.add(news.getUser_id());
		params.add(pub_time);
		System.out.println("我是新增新闻的"+news.getNews_type()+"  "+news.getUser_id());
		boolean flag=util.update(sql, params);
		util.close();
		return flag;
	}
	/**
	 * 根据id查询新闻
	 * @param id
	 * @return
	 */
	public News selectById(int id){
		try {
			JDBCUtil util=JDBCUtil.getInstance();
			String sql ="select * from tb_news where id=?";
			List<Object> params =new ArrayList<>();
			params.add(id);
			ResultSet rs =util.query(sql,params);
			System.out.println("我是查询新闻的"+id);
			if(rs.next()){
				String title=rs.getString("title");
				String content=rs.getString("content");
				String image_url=rs.getString("image_url");
				String news_type=rs.getString("news_type");
				String user_id=rs.getString("user_id");
				News news =new News();
				news.setTitle(title);
				news.setContent(content);
				news.setImage_url(image_url);
				news.setNews_type(news_type);
				news.setUser_id(Integer.parseInt(user_id));
				news.setId(id);
				System.out.println("我是查询新闻的"+news_type+"  "+Integer.parseInt(user_id));
				util.close();
				return news;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 根据id更新新闻
	 * @param news
	 * @return
	 */
	public boolean updateNews(News news){
		JDBCUtil util =JDBCUtil.getInstance();
		String sql ="update tb_news set title=?,content=?,image_url=?,news_type=?,user_id=? where id=?";
		List<Object> params= new ArrayList<>();
		params.add(news.getTitle());
		params.add(news.getContent());
		params.add(news.getImage_url());
		params.add(news.getNews_type());
		params.add(news.getUser_id());
		params.add(news.getId());
		System.out.println("我是更新新闻的"+news.getNews_type());
		boolean flag =util.update(sql, params);
		util.close();
		return flag;
	}
	/**
	 * 根据id删除新闻
	 * @param id
	 * @return
	 */
	public boolean deleterNews(int id){
		JDBCUtil util =JDBCUtil.getInstance();
		String sql ="delete from tb_news where id =?";
		List<Object> params =new ArrayList<>();
		params.add(id);
		boolean flag = util.update(sql, params);
		util.close();
		return flag;
	}
	
	/**
	 * 查询某个类型新闻
	 * @param type
	 * @return
	 */
	public List<News> querynews(int type){
		try {
			String sql="select * from tb_news where news_type=?";
			List<Object> params=new ArrayList<Object>();
			params.add(type);
			ResultSet rs=util.query(sql,params);
			List<News> news=new ArrayList<News>();
			while (rs.next()) {
				News new1=new News();
				new1.setId(rs.getInt("id"));
				new1.setTitle(rs.getString("title"));
				new1.setContent(rs.getString("content"));
				new1.setImage_url(rs.getString("image_url"));
				new1.setNews_type(rs.getString("news_type"));
				new1.setUser_id(rs.getInt("user_id"));
				new1.setPub_time(rs.getDate("pub_time"));
				news.add(new1);
			}
			return news;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			util.close();
		}
	}	
	
	/**
	 * 通过新闻id查询新闻
	 * @param newid
	 * @return
	 */
	public News queryNewById(int newid){
		JDBCUtil util=JDBCUtil.getInstance();
		try {
			String sql="select tb_news.* , tb_user.username from tb_news,tb_user where tb_news.id=? and tb_news.user_id=tb_user.id";
			List<Object> params=new ArrayList<Object>();
			params.add(newid);
			ResultSet rs=util.query(sql,params);
			News new1=new News();
			 if(rs.next()) {
				new1.setId(rs.getInt("id"));
				new1.setTitle(rs.getString("title"));
				new1.setContent(rs.getString("content"));
				new1.setImage_url(rs.getString("image_url"));
				new1.setNews_type(rs.getString("news_type"));
				new1.setUser_id(rs.getInt("user_id"));
				new1.setUsername(rs.getString("username"));
				new1.setPub_time(rs.getDate("pub_time"));
			}
			return new1;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			util.close();
		}
	}
}
